const pool = require("../db/conn");
const countControllers = require("../controllers/countControllers");

exports.selectList = async function (pageno,pagesize,cname,tname,department) {    
    let sql =   " SELECT "+
                " 	rc_course.course_id,  "+
                " 	rc_course.course_name,  "+
                " 	rc_course.course_grade,  "+
                " 	rc_course.course_time,  "+
                " 	rc_course.course_location,  "+
                " 	rc_course.course_credit,  "+
                " 	rc_course.course_selected_count,  "+
                " 	rc_course.course_max_size,  "+
                " 	rc_course.course_exam_date,  "+
                " 	rc_course.course_exam_location,  "+
                " 	rc_teacher.teacher_id,  "+
                " 	rc_teacher.teacher_name,  "+
                " 	rc_department.department_name "+
                " FROM "+
                " 	rc_course "+
                " 	LEFT JOIN "+
                " 	rc_teacher "+
                " 	ON  "+
                " 		rc_course.course_teacher_id = rc_teacher.teacher_id "+
                " 	LEFT JOIN "+
                " 	rc_department "+
                " 	ON  "+
                " 		rc_teacher.teacher_department_id = rc_department.department_id "+
                " WHERE "+
                " 	rc_course.course_is_delete = 0  ";
    let params = [];
    if(cname!=null&&cname!=""){
        sql += "AND rc_course.course_name like ? "
        params.push( `%${cname}%` );
    }
    if(tname!=null&&tname!=""){
        sql += "AND rc_teacher.teacher_name like ? "
        params.push( `%${tname}%` );
    }
    if(department!=null&&department!=""){
        sql += "AND rc_department.department_name like ? "
        params.push( `%${department}%` );
    }

    let data = await countControllers.selectCount( sql,params );
    
    sql += "LIMIT ?, ?";
    let start = ((pageno-1)*pagesize)+"";
    let size = pagesize;
    
    params.push(start,size);

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();

    data.pageNo = pageno*1
    data.pageSize = pagesize*1;
    data.list = result;
    return data;
}


exports.selectListByTeacherId = async function(tid) {
    let sql =   " SELECT "+
                " 	rc_course.course_id,  "+
                " 	rc_course.course_name,  "+
                " 	rc_course.course_grade,  "+
                " 	rc_course.course_time,  "+
                " 	rc_course.course_location,  "+
                " 	rc_course.course_credit,  "+
                " 	rc_course.course_selected_count,  "+
                " 	rc_course.course_max_size,  "+
                " 	rc_course.course_exam_date,  "+
                " 	rc_course.course_exam_location,  "+
                " 	rc_teacher.teacher_id,  "+
                " 	rc_teacher.teacher_name,  "+
                " 	rc_teacher.teacher_id "+
                " FROM "+
                " 	rc_course "+
                " 	LEFT JOIN "+
                " 	rc_teacher "+
                " 	ON  "+
                " 		rc_course.course_teacher_id = rc_teacher.teacher_id "+
                " WHERE "+
                " 	rc_course.course_is_delete = 0 AND "+
                " 	rc_teacher.teacher_is_delete = 0 AND "+
                "     rc_course.course_teacher_id = ? ";
    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,[tid]);
    conn.release();
    return result
}


exports.selectListByChoose = async function (pageno,pagesize,cname,tname) {
    let sql =   " SELECT "+
                " 	rc_course.course_id,  "+
                " 	rc_course.course_name,  "+
                " 	rc_course.course_grade,  "+
                " 	rc_course.course_time,  "+
                " 	rc_course.course_location,  "+
                " 	rc_course.course_credit,  "+
                " 	rc_course.course_selected_count,  "+
                " 	rc_course.course_max_size,  "+
                " 	rc_course.course_exam_date,  "+
                " 	rc_course.course_exam_location,  "+
                " 	rc_teacher.teacher_id,  "+
                " 	rc_teacher.teacher_name "+
                " FROM "+
                " 	rc_course "+
                " 	LEFT JOIN "+
                " 	rc_teacher "+
                " 	ON  "+
                " 		rc_course.course_teacher_id = rc_teacher.teacher_id "+
                " WHERE "+
                " 	rc_course.course_is_delete = 0 AND "+
                " 	rc_course.course_selected_count < rc_course.course_max_size ";
    let params = [];
    if(cname!=null&&cname!=""){
        sql += "AND rc_course.course_name like ? "
        params.push( `%${cname}%` );
    }
    if(tname!=null&&tname!=""){
        sql += "AND rc_teacher.teacher_name like ? "
        params.push( `%${tname}%` );
    }

    let data = await countControllers.selectCount( sql,params );
    
    sql += "LIMIT ?, ?";
    let start = ((pageno-1)*pagesize)+"";
    let size = pagesize;
    
    params.push(start,size);

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();

    data.pageNo = pageno*1
    data.pageSize = pagesize*1;
    data.list = result;
    return data;
}

exports.selectListByStudentId = async function (sid,pageno,pagesize) {
    let sql =   " SELECT "+
                " 	rc_course.course_name,  "+
                " 	rc_student_course.sc_id "+
                " FROM "+
                " 	rc_student_course "+
                " 	INNER JOIN "+
                " 	rc_course "+
                " 	ON  "+
                " 		rc_student_course.sc_course_id = rc_course.course_id "+
                " WHERE "+
                " 	rc_student_course.sc_student_id = ? ";
    let params = [ sid ];       
    let data = await countControllers.selectCount( sql,params );

    sql += "LIMIT ?, ?";
    let start = ((pageno-1)*pagesize)+"";
    let size = pagesize;
    
    params.push(start,size);

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();

    data.pageNo = pageno*1
    data.pageSize = pagesize*1;
    data.list = result;
    return data;
}

exports.selectById = async function (course_id) {
    let sql =   " SELECT "+
                " 	rc_course.course_id,  "+
                " 	rc_course.course_teacher_id,  "+
                " 	rc_course.course_name,  "+
                " 	rc_course.course_grade,  "+
                " 	rc_course.course_time,  "+
                " 	rc_course.course_location,  "+
                " 	rc_course.course_credit,  "+
                " 	rc_course.course_selected_count,  "+
                " 	rc_course.course_max_size,  "+
                " 	rc_course.course_exam_date,  "+
                " 	rc_course.course_exam_location,  "+
                " 	rc_course.course_is_delete "+
                " FROM "+
                " 	rc_course "+
                " WHERE "+
                "	rc_course.course_id = ? ";

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,[course_id]);
    conn.release();
    
    return result[0];
}



exports.insert = async function (params,state=0) {
    let sql =  "insert into rc_course values (null,?,?,?,?,?,?,?,?,?,?,?)";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[  params.course_teacher_id,
                                                    params.course_name,
                                                    params.course_grade,
                                                    params.course_time,
                                                    params.course_location,
                                                    params.course_credit,
                                                    "0",
                                                    params.course_max_size,
                                                    params.course_exam_date,
                                                    params.course_exam_location,
                                                    state 
                                                ]);
    conn.release();

    return changedRows!=0;
}

exports.updateByDid = async function (params,state=0) {
    let sql =   " UPDATE "+
                " 	rc_course"+
                " SET "+
                " 	rc_course.course_teacher_id = ?, "+
                " 	rc_course.course_name = ?, "+
                " 	rc_course.course_grade = ?, "+
                " 	rc_course.course_time = ?, "+
                " 	rc_course.course_location = ?, "+
                " 	rc_course.course_credit = ?, "+
                " 	rc_course.course_max_size = ?, "+
                " 	rc_course.course_exam_date = ?, "+
                " 	rc_course.course_exam_location = ?, "+
                " 	rc_course.course_is_delete = ? "+
                " WHERE "+
                " 	rc_course.course_id = ? ";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[  params.course_teacher_id,
                                                    params.course_name,
                                                    params.course_grade,
                                                    params.course_time,
                                                    params.course_location,
                                                    params.course_credit,
                                                    params.course_max_size,
                                                    params.course_exam_date,
                                                    params.course_exam_location,
                                                    state,
                                                    params.course_id
                                                 ]);
    conn.release();

    return changedRows!=0;
}

exports.deleteByDid = async function (course_id) {
    let sql =  "UPDATE rc_course SET course_is_delete = 1 WHERE course_id = ?";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[course_id]);
    conn.release();

    return changedRows!=0;
}



